package gre.english.vocab;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class VocabDBHelper{

	public static final String KEY_ROWID = "_id";
	public static final String KEY_VALUE = "value";	
	public static final String KEY_MEANING = "meaning";
	public static final String KEY_SYNONYM = "value";	
	public static final String KEY_SENTENCE = "sentence";
	public static final String KEY_MNEMONIC = "mnemonic";
	
	public static final Map<String, Map<String, Object>> WORDLIST = 
			new HashMap<String, Map<String, Object>>();
	public static final ArrayList<String> WORDS = new ArrayList<String>();
	
	
	private static final String DATABASE_NAME = "gre";
	private static final String DATABASE_TABLE = "word";
	// private static final int OLD_DATABASE_VERSION = 2; DO NOT ERASE THIS FIELD, IT SHOWS THE LAST VERSION USED FOR DB
	private static final int NEW_DATABASE_VERSION = 3;	
	
	private DBHelper ourHelper;
	private final Context ourContext;
	private SQLiteDatabase ourDatabase;
	
	private static class DBHelper extends SQLiteOpenHelper {	
		public DBHelper(Context context) {
			super(context, DATABASE_NAME, null, NEW_DATABASE_VERSION);
			// TODO Auto-generated constructor stub
		}
	
		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			
			db.execSQL("CREATE TABLE word (_id INTEGER PRIMARY KEY AUTOINCREMENT, value TEXT NOT NULL, meaning TEXT NOT NULL, synonym TEXT, sentence TEXT, mnemonic TEXT);");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abase',' lower; degrade; humiliate; make humble; make (oneself) lose self-respect');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abash',' embarrass');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abate',' subside or moderate');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abbreviate',' shorten');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abdicate',' renounce; give up (position, right, or responsibility)');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('aberrant',' abnormal or deviant');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('aberration',' deviation from the normal; mental disorder');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abet',' assist usually in doing something wrong; encourage');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abeyance',' suspended action');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abhor',' detest; hate');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abide',' dwell; abide by: comply with; put up with; tolerate; Ex. abide by the rules; Ex. I can''t abide rude people.');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abject',' (of a condition) wretched; as low as possible; lacking pride; very humble; showing lack of self-respect; Ex. abject apology');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abjure',' renounce upon oath');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('ablution',' washing');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abnegation',' renunciation; self-sacrifice; self-abnegation');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abode',' dwelling place; home');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abolish',' cancel; put an end to');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abominable',' detestable; extremely unpleasant');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abominate',' loathe; hate');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('aboriginal',' being the first of its kind in a region; primitive; native; indigenous; N. aborigine');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abortive',' unsuccessful; fruitless');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abrasive',' rubbing away; tending to grind down');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abridge',' condense or shorten');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abrogate',' abolish');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abscission',' cutting off; separation');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abscond',' depart secretly and hide');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('absolute',' complete; totally unlimited; having complete power; certain; not relative; Ex. absolute honesty/ruler; CF. absolutism');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('absolve',' pardon (an offense)');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abstain',' refrain; withhold from participation; intentionally not use one''s vote;');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abstemious',' sparing in eating and drinking; temperate');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abstinence',' restraint from pleasant things, esp. eating or drinking; CF. abstention: act of abstaining from vote');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abstract',' theoretical; not concrete; nonrepresentational');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abstruse',' obscure; profound; difficult to understand');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abusive',' coarsely insulting; physically harmful');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abut',' border upon; adjoin');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abysmal',' bottomless');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('abyss',' enormous chasm; vast bottomless pit');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('academic',' related to a school; not practical or directly useful');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('accede',' agree');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('accelerate',' move faster');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('accessible',' easy to approach; obtainable');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('accessory',' additional object; useful but not essential thing');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acclaim',' applaud; praise; greet with great approval; announce with great approval; Ex. The new drung has been acclaimed as the most important discoveries for years; N: strong expression of approval and praise');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acclimate',' adjust to climate or environment; adapt');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acclivity',' sharp upslope of a hill; OP. declivity');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('accolade',' award of merit; strong praise and approval');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('accommodate',' oblige or help someone; adjust or bring into harmony; adapt; make enough space for; ADJ. accommodative; CF. accomodating: helpful and obliging');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('accomplice',' partner in crime');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('accord',' agreement');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('accost',' approach and speak first to a person');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('accoutre',' equip; N. accoutrement');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('accretion',' growth or increase in size by gradual addition; growth; increase; increase by natural growth; Ex. towers and other accretions of the castle; V. accrete');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('accrue',' come to one as a gain; accumulate over time; come about by addition; Ex. benefits that accrue from scientific research; Ex. interest accruing in a bank account; N. accrual');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acerbity',' bitterness of speech and temper; ADJ. acerbic: bitter; acrid (in taste, manner, or tone)');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acetic',' vinegary');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acidulous',' slightly sour (in taste or manner); sharp; caustic');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acknowledge',' recognize; admit');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acme',' peak; pinnacle; highest point');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acne',' skin disease (on the face)');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acoustics',' science of sound; quality that makes a room easy or hard to hear in');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acquiesce',' assent; agree passively; comply without protest');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acquittal',' deliverance from a charge; V. acquit: free from a charge or accusation; discharge from a duty; conduct (oneself) in a specified manner');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acrid',' bitter (to the taste or smell); sharp; bitterly pungent');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acrimonious',' stinging; caustic; bitter in words or manner; N. acrimony: bitter ill-natured animosity in speech or behavior');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acrophobia',' fear of heights');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('actuarial',' calculating; pertaining to insurance statistics');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('actuary',' someone who advises insurance companies');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('actuate',' motivate; activate; cause to act');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acuity',' sharpness (of mind or senses of sight or hearing)');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acumen',' mental keenness; sharpness of judgment; ability to judge quickly and well; Ex. business acumen');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('acute',' (of the senses) sharp; quickly perceptive; keen; penetrating; brief and severe; Ex. acute sense of smell/analysis/pain');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adage',' wise saying; proverb');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adamant',' hard; inflexible');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adapt',' make or become suitable for a specific use; alter; modify; adjust; N. adaptation: act of adapting; composition recast into a new form; Ex. The play is an adaption of a short novel.');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('addendum',' addition; appendix to book; something that is added (as at the end of a speech or book)');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('addiction',' compulsive habitual need');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('addle',' make or become confused; muddle; drive crazy; become rotten (egg)');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('address',' direct a speech to; speak to; deal with or discuss; direct one''s efforts or attention to; make with a destination; N: formal speech');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adept',' expert at; very skilled');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adhere',' stick fast; be a devotd follower; N. adhesion: adhering; devotion; loyality');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adherent',' supporter; follower');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adjacent',' adjoining; neighboring; close by');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adjoin',' be next to');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adjourn',' suspend until a later time; move from one place to another');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adjunct',' something attached to but holding an inferior position');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adjuration',' solemn urging; V. adjure: entreat earnestly; enjoin solemnly');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adjutant',' staff officer assisting the commander; assistant');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('admonish',' warn or speak to with gentle disapproval; reprove');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adore',' love deeply and respect highly');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adorn',' decorate');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adroit',' skillful (in using mind or hand)');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adulation',' flattery; admiration that is more than is necessary or deserved');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adulterate',' make impure or of poorer quality by adding inferior or tainted(contaminated) substances');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('Advent',' arrival');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adventitious',' accidental; casual; happening by chance');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adversary',' opponent; enemy');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adverse',' going against; opposing; unfavorable; hostile');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('adversity',' great hardship or affliction; misfortune; calamitous event');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('advert',' refer');");
			db.execSQL("INSERT INTO word(value, meaning) VALUES('advocacy',' support; active pleading on behalf of someone or something');");
		}
	
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS WORD");
			onCreate(db);
		}		
	}
	
	public VocabDBHelper(Context c) {
		ourContext = c;
	}

	public VocabDBHelper open() {
		ourHelper = new DBHelper(ourContext);
		ourDatabase = ourHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		ourHelper.close();
	}
	
	public void loadData() {
		open();
		getAllData();
		close();
	}
	
	
	public void getAllData()
	{
 
		// this is a database call that creates a "cursor" object.
		// the cursor object store the information collected from the
		// database and is used to iterate through the data.
		Cursor cursor;
	 
		try
		{
			// ask the database object to create the cursor.
			cursor = ourDatabase.query(
					DATABASE_TABLE,
					new String[]{KEY_ROWID, KEY_VALUE, KEY_MEANING},
					null, null, null, null, null
			);
	 
			// move the cursor's pointer to position zero.
			cursor.moveToFirst();
	 
			// if there is data after the current cursor position, add it
			// to the ArrayList.
			if (!cursor.isAfterLast())
			{
				do
				{
					// Creating a HashMap with Key as the ColumnName and Value as its Value
					Map<String, Object> wordMap = new HashMap<String, Object>();	 
					wordMap.put(KEY_ROWID, cursor.getLong(0));
					wordMap.put(KEY_MEANING, cursor.getString(2));
	 
					// Todo: We can also take all the keys from the WordList and then
					// create this array of words rather than adding there
					//
					//
					// Adding a Word to the global Array of Words
					String word = cursor.getString(1);
					WORDS.add(word);
					
					// Finally creating a HashMap with Key as a Word and 
					// Value as a HashMap created above
					// It should look like this 
					// WORDLIST =   {	'word' : 
					//							{  'meaning' : 'word_meaning' }
					//				}
					WORDLIST.put(word, wordMap);
				}
				// move the cursor's pointer up one position.
				while (cursor.moveToNext());
			}
		}
		catch (SQLException e)
		{
			Log.e("DB Error", e.toString());
			e.printStackTrace();
		}
	}
}
